import pandas as pd

# 加载xlsx
def load_sheet(xl_path, sheet_name):
    df = pd.read_excel(xl_path, sheet_name=sheet_name)
    return df

# 
def merge_rows(df, same_names, same_forms = [], target = ['comm_name', 'comm_form']):
    # 
    for same_name in same_names:

        if len(same_forms) == 0:
            slec_df = df.loc[df[column_names[target[0]]].isin(same_name)]
            if slec_df.empty:
                print("Attention: There is no need to merge rows")
                continue

            slec_df_merged = pd.concat([slec_df.iloc[i] for i in range(len(slec_df))], axis=1).transpose().agg('sum')
            slec_df_merged[column_names[target[0]]] = same_name[0]
            # update df
            print("Attention: We will merge: ")
            print(slec_df)
            df = df.drop(slec_df.index)
            df = pd.concat([df, slec_df_merged.to_frame().transpose()], ignore_index=True)

        for same_form in same_forms:
            slec_df = df.loc[df[column_names[target[0]]].isin(same_name)]
            if df.empty:
                print("Attention: There is no need to merge rows")
                continue

            slec_rows  = slec_df.loc[slec_df[column_names[target[1]]] == same_form]
            # 
            merged_row = pd.concat([slec_rows.iloc[i] for i in range(len(slec_rows))], axis=1).transpose().agg('sum')
            merged_row[column_names[target[0]]] = same_name[0]
            merged_row[column_names[target[1]]] = same_form
            # update df
            print("Attention: We will merge: ")
            print(slec_rows)
            df = df.drop(slec_df.loc[slec_rows.index].index)
            df = pd.concat([df, merged_row.to_frame().transpose()], ignore_index=True)
        
    return df


def gen_summary(summary_path, excel_path):
    df_od = load_sheet(excel_path, '订单明细')
    df_qp = load_sheet(excel_path, '渠道利润率')
    df_cp = load_sheet(excel_path, '商品利润率')
    sale_mny = df_cp.loc[df_cp["商品名称"] == "总计", "销售金额（供货价X数量+运费）"].iloc[0]
    comm_num = df_cp.loc[df_cp["商品名称"] == "总计", "商品数量"].iloc[0]
    profit = df_cp.loc[df_cp["商品名称"] == "总计", "利润"].iloc[0]
    profit_rate = df_cp.loc[df_cp["商品名称"] == "总计", "利润率"].iloc[0]
    order_num = df_od.shape[0]
    with open(summary_path, "w") as f:
        f.write("【销售报表】：")
        f.write(str(pd.to_datetime(df_od["下单时间"]).dt.date[0]) + '\n')
        f.write("【汇总数据】\n")
        f.write("销售总金额：")
        f.write(str(sale_mny) + '\n')
        f.write("总订单数量：")
        f.write(str(order_num) + '\n')
        f.write("总商品数量：")
        f.write(str(comm_num) + '\n')
        f.write("总利润：")
        f.write(f'{profit:.2f}' + '\n')
        f.write("总利润率")
        f.write(str(profit_rate) + '\n')
        
        f.write('\n')
        f.write("【明细数据】：销售额前10渠道 \n")

        for i, row in df_qp.head(10).iterrows():
            f.write(f'{i+1}.{row["渠道"]}：{row["商品数量"]}件 销售额{row["销售金额（供货价X数量+运费）"]:.2f}元 利润{row["利润"]:.2f}元 利润率{row["利润率"]}' + '\n')

        f.write('\n')
        f.write("【明细数据】商品销售量前10 \n")

        for i, row in df_cp.head(10).iterrows():
            f.write(f'{i+1}.{row["商品名称"]}-{row["商品规格"]}：{row["商品数量"]}件 销售额{row["销售金额（供货价X数量+运费）"]:.2f}元 利润{row["利润"]:.2f}元 利润率{row["利润率"]}'  + '\n')


# comm: commodity
column_names = {
    "comm_name": "商品名称",
    "comm_form": "商品规格",
    "comm_quat": "商品数量",
    "cost": "成本（成本价X数量+供应商运费）",
    "dpot_loss": "仓库赔付",
    "sale_amnt": "销售金额（供货价X数量+运费）",
    "chan": "渠道",
    "chan_loss": "渠道赔付",
    "chan_pric": "渠道价格",
    "chan_fare": "渠道运费",
    "distributor": "分销商",
    "summary": "总计",
    "profit": "利润",
    "profit_rate": "利润率",
}

comm_same_names = [
    ['蛮嗨0糖黑芝麻丸498g/袋', 'T蛮嗨0糖芝麻丸498g/袋'],
]

comm_same_forms = [
    '3袋',
    '1袋'
]

chan_expend_names = [
    '争渡品牌管理(220302358) - 1668516223-供货商'
]

chan_same_names = [
]

def update_chan_same_names(path):
    df = pd.read_excel(path)
    global chan_same_names 
    chan_same_names = df.values.tolist()


